/*
 * Copyright (C) The Apache Software Foundation. All rights reserved.
 *
 * This software is published under the terms of the Apache Software License
 * version 1.1, a copy of which has been included with this distribution in
 * the LICENSE file.
 */
package ranab.server.ftp.usermanager;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;

import ranab.server.ftp.FtpConfig;
import ranab.util.StringUtils;


/**
 * This is another database based user manager class. I have
 * tested it using MySQL and Oracle database. The table structure 
 * file : </code>apps/ftp/conf/ftp-db.sql</code>
 *
 * All the user attributes are replaced during run-time. So we can use
 * your database schema. Then you need to modify the SQLs in the configuration
 * file.
 *
 * @author <a href="mailto:rana_b@yahoo.com">Rana Bhattacharyya</a>
 */
public
class DbUserManager extends UserManager {

    private Connection mDbConnection       = null;

    private String mInsUserStmt = null;
    private String mDelUserStmt = null;
    private String mSelUserStmt = null;
    private String mGetAllStmt  = null;
    private String mUpdUserStmt = null;
    
    private String mUrl      = null;
    private String mUser     = null;
    private String mPassword = null;
    
    
    /**
     * Constructor. Create database connection and
     * create statements.
     */
    public DbUserManager(FtpConfig cfg) throws Exception {
        super(cfg);

        String className = cfg.getProperty(FtpConfig.PREFIX + "db.driver");
        Class.forName(className);

        mUrl          = cfg.getProperty(FtpConfig.PREFIX + "db.url");
        mUser         = cfg.getProperty(FtpConfig.PREFIX + "db.user");
        mPassword     = cfg.getProperty(FtpConfig.PREFIX + "db.password");
        mInsUserStmt  = cfg.getProperty(FtpConfig.PREFIX + "db.sql.user.insert");
        mDelUserStmt  = cfg.getProperty(FtpConfig.PREFIX + "db.sql.user.delete");
        mSelUserStmt  = cfg.getProperty(FtpConfig.PREFIX + "db.sql.user.select");
        mGetAllStmt   = cfg.getProperty(FtpConfig.PREFIX + "db.sql.user.all");
        mUpdUserStmt  = cfg.getProperty(FtpConfig.PREFIX + "db.sql.user.update");
        
        openConnection();
        getConfig().getLogger().info("Database user manager opened.");
    }
    
    
    /**
     * Open connection to database.
     */
    private void openConnection() throws SQLException, IOException {
        
        // get connection
        mDbConnection = DriverManager.getConnection(mUrl, mUser, mPassword);
        mDbConnection.setAutoCommit(true);
    }
    
    
    /**
     * Close connection to database.
     */
    private void closeConnection() {
        if (mDbConnection != null) {        
            try {mDbConnection.close(); } catch(SQLException ex) {}
            mDbConnection = null;
        }

        getConfig().getLogger().info("Connection closed.");
    }
    
    
    /**
     * Prepare connection to database.
     */
    private void prepareConnection() throws IOException, SQLException {
        boolean closed = false;
        
        try {
            if( (mDbConnection == null) || mDbConnection.isClosed() ) {
                closed = true;
            }
        }
        catch(SQLException ex) {
            closed = true;
        }

        if (closed) {
            closeConnection();
            openConnection();
        }
    }
    
    
    /**
     * Delete user. Delete the row from the table.
     */
    public synchronized void delete(String name) throws IOException, SQLException {
        HashMap map = new HashMap();
        map.put(User.ATTR_LOGIN, name);
        String sql = StringUtils.replaceString(mDelUserStmt, map);
        
        prepareConnection();
        Statement stmt = mDbConnection.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();
    }

    /**
     * Save user. If new insert a new row, else update the existing row.
     */
    public synchronized void save(User user) throws IOException, SQLException {
        
        // null value check
        if(user.getName() == null) {
            throw new NullPointerException("User name is null.");
        } 
        
        prepareConnection();   
        
        HashMap map = new HashMap();
        map.put(User.ATTR_LOGIN, user.getName());
        map.put(User.ATTR_PASSWORD, getPassword(user));
        map.put(User.ATTR_HOME, user.getVirtualDirectory().getRootDirectory());
        map.put(User.ATTR_ENABLE, String.valueOf(user.getEnabled()));
        map.put(User.ATTR_WRITE_PERM, String.valueOf(user.getVirtualDirectory().getWritePermission()));
        map.put(User.ATTR_MAX_IDLE_TIME, new Long(user.getMaxIdleTime()));
        map.put(User.ATTR_MAX_UPLOAD_RATE, new Integer(user.getMaxUploadRate()));
        map.put(User.ATTR_MAX_DOWNLOAD_RATE, new Integer(user.getMaxDownloadRate())); 
        
        String sql = null;      
        if( !doesExist(user.getName()) ) {
            sql = StringUtils.replaceString(mInsUserStmt, map);
        }
        else {
            sql = StringUtils.replaceString(mUpdUserStmt, map);
        }
        
        Statement stmt = mDbConnection.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();
    }


    /**
     * Get the user object. Fetch the row from the table.
     */
    public synchronized User getUserByName(String name) {
        
        Statement stmt = null;
        ResultSet rs = null;
        try {
            User thisUser = null;
            HashMap map = new HashMap();
            map.put(User.ATTR_LOGIN, name);
            String sql = StringUtils.replaceString(mSelUserStmt, map);

            prepareConnection();
            stmt = mDbConnection.createStatement();
            rs = stmt.executeQuery(sql);
            
            if(rs.next()) {
                thisUser = new User();
                thisUser.setName(rs.getString(1));
                thisUser.getVirtualDirectory().setRootDirectory(new File(rs.getString(3)));
                thisUser.setEnabled(rs.getString(4).equals(Boolean.TRUE.toString()));
                thisUser.getVirtualDirectory().setWritePermission(rs.getString(5).equals(Boolean.TRUE.toString()));
                thisUser.setMaxIdleTime(rs.getInt(6));
                thisUser.setMaxUploadRate(rs.getInt(7));
                thisUser.setMaxDownloadRate(rs.getInt(8));
            }
            return thisUser;
        }
        catch(Exception ex) {
            getConfig().getLogger().error(ex);
        }
        finally {
            if(rs != null) {
                try { rs.close(); } catch(Exception ex) {}
            }
            if(stmt != null) {
                try { stmt.close(); } catch(Exception ex) {}
            }
        }
        
        return null;
    }
    
    
    /**
     * User existance check
     */
    public synchronized boolean doesExist(String name) {
        boolean bValid = false;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            HashMap map = new HashMap();
            map.put(User.ATTR_LOGIN, name);
            String sql = StringUtils.replaceString(mSelUserStmt, map);
            
            prepareConnection();
            stmt = mDbConnection.createStatement();
            rs = stmt.executeQuery(sql);
            bValid = rs.next();
            
        }
        catch(Exception ex) {
            bValid = false;
            getConfig().getLogger().error(ex);
        }
        finally {
            if(rs != null) {
                try { rs.close(); } catch(Exception ex) {}
            }
            if(stmt != null) {
                try { stmt.close(); } catch(Exception ex) {}
            }
        }
        
        return bValid;
    }
    
    
    /**
     * Get all user names from the database.
     */
    public synchronized Collection getAllUserNames() {
        ArrayList names = new ArrayList();
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            String sql = mGetAllStmt;
            
            prepareConnection();
            stmt = mDbConnection.createStatement();
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                names.add(rs.getString(1));
            }
        }
        catch(Exception ex) {
            getConfig().getLogger().error(ex);
        }
        finally {
            if(rs != null) {
                try { rs.close(); } catch(Exception ex) {}
            }
            if(stmt != null) {
                try { stmt.close(); } catch(Exception ex) {}
            }
        }
        
        return names;
    }

    
    /**
     * Get user password.
     * <pre>
     * If the password value is not null
     *    password = new password 
     * else 
     *   if user does exist
     *     password = old password
     *   else 
     *     password = ""
     * </pre>
     */
    private synchronized String getPassword(User user) throws IOException, SQLException {
        
        if (user.getPassword() != null) {
            return user.getPassword();
        }
        
        String password = "";
        HashMap map = new HashMap();
        map.put(User.ATTR_LOGIN, user.getName());
        String sql = StringUtils.replaceString(mSelUserStmt, map);
        
        prepareConnection();
        Statement stmt = mDbConnection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()) {
            password = rs.getString(2);
        }
        rs.close();
        stmt.close();
        
        if (password == null) {
            password = "";
        }
        return password;
    }
    
    
    /**
     * User authentication
     */
    public synchronized boolean authenticate(String user, String password) {
        
        String existPassword = null;
        
        try {
            HashMap map = new HashMap();
            map.put(User.ATTR_LOGIN, user);
            String sql = StringUtils.replaceString(mSelUserStmt, map);
            
            prepareConnection();
            Statement stmt = mDbConnection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                existPassword = rs.getString(2);
            }
            rs.close();
            stmt.close();
        }
        catch(Exception ex) {
            getConfig().getLogger().error(ex);
            return false;
        }
        
        if (existPassword == null) {
            existPassword = "";
        }
        
        return existPassword.equals(password);
    }
    
    /**
     * Close this user manager. Close the database statements and connection.
     */
    public synchronized void dispose() {
        closeConnection();
    }    
}





